#!/bin/bash
#设置任务异常退出
set -eo pipefail
dt='{{ execution_date | cst_ds }}'
name='进港转运及时率汇总'
# 在退出时清理文件
trap "rm -f /tmp/${name}_${dt}.zip /tmp/${name}_${dt}.csv && echo 'removed tmp files'" EXIT
# 创建txt文件
touch /tmp/${name}_${dt}.txt
#将中文字段名输入文件顶部
#将查询结果追加到文件中
{
  echo -e "管理大区code\t管理大区名称\t代理区code\t代理区名称\t目的中心code\t目的中心名称\t操作票数\t转运及时票数\t转运及时率\t转运不及时票数\t无到车打卡/无到件\t无二段码\t末端机构不符\t发件目的网点无路由\t路由不符\t拦截退转件\t发件下一站无路由\t操作错分\t其他\t发件日期\t">/tmp/${name}_${dt}.txt;
  spark-sql --driver-memory 8G --num-executors 8 --executor-memory 12g --executor-cores 6  --conf spark.executor.memoryOverhead=4g --conf spark.driver.maxResultSize=3g -S -e "
with tmp_waybill as (
select
waybill_no,
express_type_name
from jms_dwd.dwd_yl_oms_oms_waybill_dt
where dt >= date_add('{{ execution_date | cst_ds }}',-15) and dt <= date_add('{{ execution_date | cst_ds }}',1)
and express_type_name = '兔优达'
)

, tmp_detail as (
select
  t1.*,t2.express_type_name
from jms_dwd.dwd_centersend_timerate_detail_dt t1
join tmp_waybill t2
  on t1.billcode = t2.waybill_no
where dt = '{{ execution_date | date_add(-1) | cst_ds }}'
)

select
        max(end_center_manage_region_code) end_center_manage_region_code                          -- 管理大区code
      , max(end_center_manage_region_name) end_center_manage_region_name                          -- 管理大区名称
      , max(end_center_agent_code)         end_center_agent_code                                  -- 代理区code
      , max(end_center_agent_name)         end_center_agent_name                                  -- 代理区名称
      , end_center_code                                                                           --目的中心code
      , max(end_center_name) end_center_name                                                      --目的中心名称
      , sum(1) opt_num                                                                            --操作票数
      , sum(case when is_timely = 1 then 1 else 0 end)                as timely_num               --转运及时票数
      , sum(case when is_timely = 1 then 1 else 0 end)/sum(1)         as timely_rate              --转运及时率
      , sum(case when is_timely in (0,2) then 1 else 0 end)           as untimely_num             --转运不及时票数
      , sum(case when untimely_type = '无到车打卡/无到件' then 1 else 0 end )  as no_car_no_arrive   --无到车打卡/无到件
      , sum(case when untimely_type = '无二段码' then 1 else 0 end )           as no_second_code    --无二段码
      , sum(case when untimely_type = '末端机构不符' then 1 else 0 end )       as last_station_miss_match   --末端机构不符
      , sum(case when untimely_type = '发件目的网点无路由' then 1 else 0 end ) as send_deststation_no_route   --发件目的网点无路由
      , sum(case when is_route = '路由不符' then 1 else 0 end )                as route_no_match   --路由不符
      , sum(case when untimely_type = '拦截退转件' then 1 else 0 end )         as inter_back   --拦截退转件
      , sum(case when untimely_type = '发件下一站无路由' then 1 else 0 end )   as send_next_no_route   --发件下一站无路由
      , sum(case when untimely_type = '操作错分' then 1 else 0 end )           as oper_miss   --操作错分
      , sum(case when untimely_type = '其他' then 1 else 0 end )               as else_num  --其他
      , send_date
from tmp_detail
where dt = '{{ execution_date | date_add(-1) | cst_ds }}'
group by send_date,end_center_code
;
 " >>/tmp/${name}_${dt}.txt
}

# 替换分隔符，并输出csv
cat /tmp/${name}_${dt}.txt | tr -s '\t' ',' > /tmp/${name}_${dt}.csv
# 压缩并删除 csv 文件
zip -jrm /tmp/${name}_${dt}.zip /tmp/${name}_${dt}.csv
# 上传文件
/usr/local/python3/bin/python3  /usr/local/airflow/dags/utils/operators/upload_oss.py ${name}/${name}_${dt}.zip /tmp/${name}_${dt}.zip
# zip 文件将在进程退出后被自动清理

#声明文件大小
data_size=$(ls -lh /tmp/${name}_${dt}.zip | awk '{print $5}')
#打印文件信息
Message="${name}_${dt}.zip 上传成功 大小: $data_size"
#配置钉钉告警
curl 'https://oapi.dingtalk.com/robot/send?access_token=816a8f35fd84432ffa6bff9d7714abc322628bb4a8ded86908550d2335aba613' \
   -H 'Content-Type: application/json' \
   -d "
  {\"msgtype\": \"text\",
    \"text\": {
        \"content\": \"监控内容:$Message\"
     }
  }" || true
rm -f  /tmp/${name}_${dt}.txt